﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BioModel;
using System.Data.SqlClient;

namespace BioDAL
{
    public class SalDAL : ISalDAL
    {

        public SalDAL()
        {
        }

        #region Connection
        private SqlConnection sqlCn = null;

        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region GetMaxID
        public int GetMaxId(string kolonneNavn, string tabel)
        {
            try
            {
                int mId = -1;
                string sql = "Select MAX(" + kolonneNavn + ") as maxID from " + tabel;
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(String.Format("{0}", dr["maxID"]));
                        mId = (int)(dr["maxID"]);
                    }
                    dr.Close();
                }
                return mId;
            }
            catch (SqlException se2)
            {
                Console.WriteLine("!SQL fejl " + se2);
                return -1;
            }
        }
        #endregion

        #region Sal
        public int InsertSal(int antalPladser, bool er3D)
        {
            try
            {
                string sql = string.Format("Insert into bioSal" +
                     "(antalPladser ,er3D) Values" +
                     "('{0}', '{1}')", antalPladser, er3D);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception se)
            {
                Console.WriteLine("!SQL Insert Sal fejl: " + se);
            }
            return GetMaxId("salNr", "bioSal");
        }

        public void EditSal(int salNr, bool er3D)
        {
            string sql = string.Format("Update bioSal Set er3D = '{0}') Where salNr = {1}"
                , er3D, salNr);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private static Sal BuildSal(SqlDataReader dr)
        {
            Sal sal = new Sal((int)(dr["salNr"]), (int)(dr["antalPladser"]), (bool)(dr["er3D"]));
            return sal;
        }

        public List<Sal> AlleSal()
        {
            List<Sal> salListe = new List<Sal>();
            string sql = string.Format("Select * From bioSal");
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    salListe.Add(BuildSal(dr));
                }
                dr.Close();
            }
            return salListe;
        }

        public List<Sal> Alle3DSal(bool status)
        {
            List<Sal> salListe = new List<Sal>();
            string sql = string.Format("Select * From bioSal where er3D = '{0}'", status);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    salListe.Add(BuildSal(dr));
                }
                dr.Close();
            }
            return salListe;
        }

        public Sal GetEnSal(int salNr)
        {
            Sal sal = new Sal();
            string sql = string.Format("Select * from bioSal where salNr = {0}", salNr);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    sal = (BuildSal(dr));

                }
                dr.Close();
            }
            return sal;
        }

        public void SletEnSal(int salId)
        {
            try
            {
                string sql = string.Format("DELETE FROM bioSal Where salNr = {0}"
                , salId);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion
    }
}
